package hedron.db;

import static hedron.i18n.Internationalization._;
import hedron.Application;
import hedron.db.model.Contact;
import hedron.db.model.DestoredStock;
import hedron.db.model.Stock;
import hedron.db.model.StoreName;
import hedron.db.model.StoredStock;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;

public class StoreManager {

	final DBManager db;
	final String[] metrics;

	public StoreManager(IDManager ID, DBManager db) {
		this.db = db;
		metrics = new String[] { _("Metrics.Metric1"), _("Metrics.Metric2"), _("Metrics.Metric3"),
				_("Metrics.Metric4"), _("Metrics.Metric5"), _("Metrics.Metric6") };
	}

	public ArrayList<StoreName> getStoreNames() {
		ArrayList<StoreName> types = new ArrayList<StoreName>();
		Connection conn = db.getConnection();
		if (conn != null) {
			try {
				StoreName storeName = new StoreName();
				StringBuilder sql = new StringBuilder();
				sql.append("select * from ");
				sql.append(storeName.getTableName());
				sql.append(" ORDER BY STORE_NAME");
				ResultSet rs = db.query(conn, sql.toString());
				if (rs != null) {
					while (db.next(rs)) {
						types.add(new StoreName(rs));
					}
					db.close(rs);
				}
			} finally {
				db.closeConnection(conn);
			}
		}
		return types;
	}

	public List<StoredStock> getStoredStocks(StoreName storeName, String filter, String sortColumn, String direction,
			boolean groupsed) {
		List<StoredStock> result = new ArrayList<StoredStock>();
		Connection conn = db.getConnection();
		if (conn != null) {
			try {
				StoredStock storedStock = new StoredStock();
				Stock stock = new Stock();
				Contact contact = new Contact();

				StringBuilder sql = new StringBuilder();
				sql.append("select sg.GROUP_NAME, st.STOCK_NAME,");
				if (groupsed) {
					sql.append(" GROUP_NAME, STOCK_NAME, 1 as ROW_ID, '' as C_NAME,");
					sql.append("SUM(s.QTY)as QTY, MAX(s.METRIC) as METRIC, MIN(DELIVERY_DATE) as DELIVERY_DATE, ");
					sql.append("SUM(s.QTY*s.PRICE_BUY) as PRICE_BUY, MAX(s.PRICE_SELL) as PRICE_SELL ");
				} else {
					sql.append(" ROW_ID, c.C_NAME, s.*");
				}
				sql.append(" from ");
				sql.append(storedStock.getTableName()).append(" s, ");
				sql.append(storeName.getTableName()).append(" sd, ");
				sql.append(stock.getTableName()).append(" st, ");
				sql.append(contact.getTableName()).append(" c, HSTOCKGROUP sg ");
				sql.append(" WHERE s.STORE_ID = ").append(storeName.getID());
				sql.append(" AND s.STORE_ID = sd.STORE_ID ");
				sql.append(" AND s.STOCK_ID = st.STOCK_ID ");
				sql.append(" AND st.GROUP_ID = sg.GROUP_ID ");
				sql.append(" AND s.CUSTOMER_ID = c.CUSTOMER_ID ");
				if (filter != null && filter.length() > 0) {
					sql.append(" AND (").append(db.prepareSearchCondition("st.SEARCHTEXT", filter));
					sql.append(" OR ").append(db.prepareSearchCondition("sd.STORE_NAME", filter));
					sql.append(" OR ").append(db.prepareSearchCondition("c.SEARCHTEXT", filter));
					sql.append(")");
				}
				if (groupsed) {
					sql.append(" GROUP BY GROUP_NAME, STOCK_NAME");
				}
				sql.append(" ORDER BY ");
				sql.append(sortColumn).append(' ').append(direction);
				if (!sortColumn.equals("STOCK_NAME")) {
					sql.append(", STOCK_NAME ASC");
				}

				ResultSet rs = db.query(conn, sql.toString());
				if (rs != null) {
					while (db.next(rs)) {
						final StoredStock stored = new StoredStock(rs);
						stored.PRICE_BUY /= stored.QTY;
						result.add(stored);
					}
					db.close(rs);
				}
			} finally {
				db.closeConnection(conn);
			}
		}
		return result;
	}

	public String[] getMetrics() {
		return metrics;
	}

	public void moveSplitStock(StoredStock stock, long storeID, long moveQty) throws Exception {
		Connection conn = db.getTransaction();
		try {
			stock.load(conn);

			if (moveQty < stock.QTY) {
				// split
				StoredStock splitted = new StoredStock();
				splitted.QTY = moveQty;
				splitted.STORE_ID = storeID;

				splitted.STOCK_ID = stock.STOCK_ID;
				splitted.CUSTOMER_ID = stock.CUSTOMER_ID;
				splitted.PRICE_BUY = stock.PRICE_BUY;
				splitted.PRICE_SELL = stock.PRICE_SELL;
				splitted.METRIC = stock.METRIC;
				splitted.DELIVERY_DATE = stock.DELIVERY_DATE;

				if (!splitted.store(conn)) throw new Exception("Cannot update stock");

				stock.QTY -= moveQty;
				if (!stock.store(conn)) throw new Exception("Cannot update stock");

			} else if (moveQty == stock.QTY) {
				// move
				stock.STORE_ID = storeID;
				if (!stock.store(conn)) throw new Exception("Cannot update stock");
			}

			db.commitTransaction(conn);
		} catch (Exception e) {
			db.rollbackTransaction(conn);
			throw e;
		} finally {
			db.closeConnection(conn);
		}
	}

	public void groupStockUpdateBuySell(StoredStock storedStock) {
		Connection conn = db.getConnection();
		if (conn != null) {
			try {
				StringBuilder sql = new StringBuilder();
				sql.append("select g.STOCK_ID, g.QTY, AVG(s.PRICE_BUY), AVG(s.PRICE_SELL) ");
				sql.append("from HGROUPING g, HSTORE s ");
				sql.append("WHERE g.STOCK_ID = s.STOCK_ID ");
				sql.append("GROUP BY g.STOCK_ID, g.QTY ");
				ResultSet rs = db.query(conn, sql.toString());

				storedStock.PRICE_BUY = 0d;
				storedStock.PRICE_SELL = 0d;
				if (rs != null) {
					while(db.next(rs)){
						storedStock.PRICE_BUY += rs.getLong(2) * rs.getDouble(3);
						storedStock.PRICE_SELL += rs.getLong(2) * rs.getDouble(4);
					}
					db.close(rs);
				}
			} catch (Exception e) {
				storedStock.PRICE_BUY = 0d;
				storedStock.PRICE_SELL = 0d;
			} finally {
				db.closeConnection(conn);
			}
		}
	}

	public void destoreStock(StoredStock selectedStock, String reason) {
		Connection conn = db.getTransaction();
		if (conn != null) {
			try {
				DestoredStock destoredStock = new DestoredStock(selectedStock, reason);

				if (!destoredStock.store(conn)) {
					throw new Exception("Cannot destore stock:" + destoredStock.getID());
				}
				if (!selectedStock.delete(conn)) {
					throw new Exception("Cannot delete stock from store:" + selectedStock.getID());
				}

				db.commitTransaction(conn);
			} catch (Exception e) {
				db.rollbackTransaction(conn);
				Application.log.log(Level.SEVERE, e.getMessage(), e);
				Application.setStatusMessage(1, e.getMessage());
			} finally {
				db.closeConnection(conn);
			}
		}
	}

	public void groupStock(long stockID, long storeID, long groupQty, double buy, double sell) throws Exception {
		Connection conn = db.getTransaction();
		try {
			StringBuilder sql = new StringBuilder();
			sql.append("select STOCK_ID, QTY FROM HGROUPING g ");
			sql.append("WHERE g.GROUP_ID = ").append(stockID);
			ResultSet rs = db.query(conn, sql.toString());
			if (rs != null) {
				while (db.next(rs)) {
					destoreStockInternal(conn, storeID, rs.getLong(1), groupQty * rs.getLong(2));
				}
				db.close(rs);
			} else {
				throw new IllegalStateException("nothing to group from!");
			}

			StoredStock groupedStock = new StoredStock();
			groupedStock.STOCK_ID = stockID;
			groupedStock.STORE_ID = storeID;
			groupedStock.CUSTOMER_ID = 0;// current company
			groupedStock.METRIC = _("Metrics.Metric1");
			groupedStock.QTY = groupQty;

			groupedStock.PRICE_BUY = buy;
			groupedStock.PRICE_SELL = sell;

			if (!groupedStock.store(conn)) {
				throw new Exception("Cannot group stock: " + stockID);
			}

			db.commitTransaction(conn);
		} catch (Exception e) {
			e.printStackTrace();
			Application.log.log(Level.WARNING, e.getMessage(), e);
			db.rollbackTransaction(conn);
			throw e;
		} finally {
			db.closeConnection(conn);
		}
	}

	protected void destoreStockInternal(Connection conn, long storeID, long stockID, long qty) throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("select * FROM HSTORE");
		sql.append(" WHERE STORE_ID = ").append(storeID);
		sql.append(" AND STOCK_ID = ").append(stockID);
		sql.append(" ORDER BY QTY ASC");
		ResultSet rs = db.query(conn, sql.toString());
		if (rs != null) {
			while (db.next(rs) && qty != 0) {
				StoredStock stock = new StoredStock(rs);
				DestoredStock destoredStock = new DestoredStock(stock, "--grouped--");

				if (qty < stock.QTY) {// need to destore partially
					stock.QTY -= qty;
					destoredStock.QTY = qty;
					if (!stock.store(conn)) {
						throw new Exception("Cannot update stock from store:" + stock.getID());
					}
					qty = 0;
				} else {// need to destore the whole stock quantity
					if (!stock.delete(conn)) {
						throw new Exception("Cannot delete stock from store:" + stock.getID());
					}
					qty -= stock.QTY;
				}

				if (!destoredStock.store(conn)) {
					throw new Exception("Cannot destore stock:" + destoredStock.getID());
				}

			}
			if (qty == 0) {
				// quantity destored match
				return;
			}
			rs.close();
		}
		throw new Exception("Cannot find enough stocks:");
	}

}
